#include "databasemanage.h"
#include<QDate>
#include<QTime>
#include<QRandomGenerator64>
#include<QRandomGenerator>
#include<QDateTime>
databasemanage::databasemanage(QWidget *parent)
    : QWidget{parent}
{
    database=QSqlDatabase::addDatabase("QSQLITE");
    database.setDatabaseName("D:\\Qt6\\QtProject\\SimulateBStation\\bs.db");
    if(!database.open())
        qDebug()<<"database not open";
    sq=new QSqlQuery(database);
    sql=new QSqlQuery(database);
}

int databasemanage::createnewudpsocket()
{
    QRandomGenerator rm(QDateTime::currentMSecsSinceEpoch());
    int socket=rm.bounded(1000,9000);
}

bool databasemanage::judgeresonable(QString account, QString secret)
{
    if(!secret.isEmpty())  //密码非空则为登入
    {
        QString str=QString("select * from privateinfo where account=%1 and secret=%2").arg(account).arg(secret);
        if(!sq->exec(str))
            qDebug()<<"databasemanage19:执行sql语句失败";
        if(!sq->first())  //查询结果为空
        {
            return false;
        }
        else
            return true;
    }
    else  //注册
    {
        QString str=QString("select * from privateinfo where account='%1'").arg(account);
        if(!sq->exec(str))
            qDebug()<<"databasemanage31:执行sql语句失败";
        if(!sq->first())  //查询结果为空
        {
            return true;
        }
        else   //查询结果不为空
            return false;
    }
}

void databasemanage::complementprivateinfo(QString account,privateinfo* pri)
{
    pri->account=account;
    QString str=QString("select * from privateinfo where account=%1").arg(account);
    if(!sq->exec(str))
        qDebug()<<"databasemanage46:执行sql语句失败";
    if(!sq->first())  //查询结果为空
    {
        qDebug()<<"databasemanage49:无此用户";
    }
    else
    {
        pri->secret=sq->value("secret").toString();
        pri->image=sq->value("image").toString();
        pri->name=sq->value("name").toString();
        pri->sign=sq->value("sign").toString();
        pri->udpsocket=sq->value("udpsocket").toInt();
        //填充用户粉丝信息
        // str=QString("select  from friendrelation where friend=%1").arg(account);
        // if(!sq->exec(str))
        //     qDebug()<<"databasemanage59:执行sql语句失败";
        // QSqlQuery* sqlfan=new QSqlQuery(database);
        // while(sq->next())
        // {
        //     QString fanname=sq->value("people").toString();
        //     QString fanstr=QString("select * from privateinfo where name=%1").arg(fanname);
        //     sqlfan->exec(fanstr);
        //     if(!sq->first())  //查询结果为空
        //     {
        //         qDebug()<<"databasemanage68:无此用户";
        //     }
        //     else
        //     {
        //         QString image=sqlfan->value("image").toString();
        //         QString sign=sqlfan->value("sign").toString();
        //         pri->addfan(fanname,image,sign);
        //     }

        // }
    }
}

void databasemanage::insertprivateinfo(QString name, QString image, QString sign, QString account, QString secret)
{
    int newudpsocket=createnewudpsocket();
    QString str=QString("insert into privateinfo (name,image,sign,account,secret,udpsocket) values('%1','%2','%3','%4','%5',%6)").arg(name).arg(image).arg(sign).arg(account).arg(secret).arg(newudpsocket);
    if(!sq->exec(str))
        qDebug()<<"databasemanage99:sql执行失败";

}

void databasemanage::updateinfo(QString table, QString probeitem, QString situation,QString attribute,QString newvalue)
{
    QString str=QString("update %1 set %2=%3 where %4=%5").arg(table).arg(attribute).arg(newvalue).arg(probeitem).arg(situation);
    if(!sq->exec(str))
        qDebug()<<"databasemanage119: sql failed";
}

void databasemanage::updatefriendrelation(QString type, QString people, QString friends)
{
    if(type=="insert")
    {
        QString str=QString("insert into friendrelation (people,friend) values('%1','%2')").arg(people).arg(friends);
        if(!sq->exec(str))
            qDebug()<<"databasemanage104:sql执行失败";
    }
    else if(type=="delete")
    {
        QString str=QString("delete from friendrelation where people='%1' and friend='%2'").arg(people).arg(friends);
        if(!sq->exec(str))
            qDebug()<<"databasemanage110:sql执行失败";
    }
}

bool databasemanage::isfriend(QString people, QString friends)
{
    QString str=QString("select * from friendrelation where people='%1' and friend='%2'").arg(people).arg(friends);
    if(!sq->exec(str))
        qDebug()<<"databasemanage118:sql执行失败";
    if(!sq->first())
        return false;
    else
        return true;
}

void databasemanage::insertvideoevaluation(int id,QString people,QString time,QString content_type,QString content,bool istalk,QString position,QString image)
{
    QString str=QString("insert into videoevaluation (id,people,time,content_type,content,istalk,position,image) values(%1,'%2','%3','%4','%5',%6,'%7','%8')").arg(id).arg(people).arg(time).arg(content_type).arg(content).arg(istalk).arg(position).arg(image);
    qDebug()<<str;
    if(!sq->exec(str))
        qDebug()<<"databasemanage129:sql执行失败";
}

void databasemanage::addhistoricalwatch(QString people, QString watchtime, QString watchlength, int videoid)
{
    QString str=QString("insert into historicalwatch (people,watchtime,watchlength,videoid) values('%1','%2','%3',%4)").arg(people).arg(watchtime).arg(watchlength).arg(videoid);
    if(!sq->exec(str))
        qDebug()<<"databasemanage134:sql执行失败";
}

void databasemanage::loadhistoricalwatch(historicalwatch *his,privateinfo* pri)
{
    QString str=QString("select * from historicalwatch where people='%1'").arg(pri->name);
    if(!sq->exec(str))
        qDebug()<<"databasemanage134:sql执行失败";
    while(sq->next())
    {
        QString watchtime=sq->value("watchtime").toString();
        QString watchlength=sq->value("watchlength").toString();
        int videoid=sq->value("videoid").toInt();
        onevideoinfo* ov=new onevideoinfo(his);
        ov->videoid=videoid;
        ov->position=watchtime;
        ov->videolength=watchlength;
        QString str1=QString("select * from videobasicinfo where id=%1").arg(videoid);
        if(!sql->exec(str1))
            qDebug()<<"databasemanage156:sql执行失败";
        while(sql->next())
        {
        ov->videoid=sql->value("id").toInt();
        ov->favornum=sql->value("favornum").toInt();
        ov->coinnum=sql->value("coinnum").toInt();
        ov->storagenum=sql->value("storagenum").toInt();
        ov->image=sql->value("image").toString();
        ov->sign=sql->value("sign").toString();
        ov->videoaddress=sql->value("address").toString();
        ov->author=sql->value("author").toString();
        ov->videolength=sql->value("videolength").toString();
        ov->issuetime=sql->value("issuetime").toString();
        ov->authorimage=sql->value("authorimage").toString();
        ov->playnum=sql->value("playnum").toInt();
        his->addhistoricalwatch(ov);
        }
    }
}

void databasemanage::complementpersonalcollect(QString people)
{

}

void databasemanage::addonecollect(privateinfo *pri, QString collectname, QString firstimage)
{
    QString people=pri->name;
    QString str=QString("insert into personalcollect( people,collectnum,videoids,collectname,firstimage) values('%1',%2,'%3','%4','%5')").arg(people).arg(0).arg("").arg(collectname).arg(firstimage);
    qDebug()<<str;
    if(!sq->exec(str))
        qDebug()<<"databasemanage186:sql执行失败";
}

void databasemanage::addonemyvideo(onevideoinfo *ones)
{
    QString people=ones->author;
    int videoid=ones->videoid;
    QString videoaddress=ones->videoaddress;
    QString videoimage=ones->image;
    QString videolength=ones->videolength;
    QString sign=ones->sign;
    QString authorimage=ones->authorimage;
    QString issuetime=ones->issuetime;
    QString str=QString("insert into personalvideos(people,videoid,videoaddress,videoimage,videolength,sign) values('%1',%2,'%3','%4','%5','%6')").arg(people).arg(videoid).arg(videoaddress).arg(videoimage).arg(videolength).arg(sign);
    if(!sq->exec(str))
        qDebug()<<"database199: insert one myvideo failed";
    //这里自定设定可将自己的视频更新到存储所有视频内容的表中
    // str=QString("insert into videobasicinfo(author,id,address,image,videolength,sign,authorimage,issuetime) values('%1',%2,'%3','%4','%5','%6','%7','%8')").arg(people).arg(videoid).arg(videoaddress).arg(videoimage).arg(videolength).arg(sign).arg(authorimage).arg(issuetime);
    // if(!sq->exec(str))
    //     qDebug()<<"database204: insert one videobasicinfo failed";

}

void databasemanage::loadmyvideo(QString people, QVector<onevideoinfo *> &ones)
{
    QString str=QString("select * from personalvideos where people='%1'").arg(people);
    if(!sq->exec(str))
        qDebug()<<"databasemanager214: load personalvideos failed";
    while(sq->next())
    {
        onevideoinfo* one=new onevideoinfo;
        one->videoid=sq->value("videoid").toInt();
        one->videoaddress=sq->value("videoaddress").toString();
        one->videolength=sq->value("videoimage").toString();
        one->author=people;
        one->sign=sq->value("sign").toString();
        one->image=sq->value("videoimage").toString();
        one->displayvideolength=false;
        one->isown=true;
        one->initUi();
        ones.push_back(one);
    }
}

void databasemanage::initcollects(QString people, QHash<QString, QVector<int>>& collects,QHash<QString,firstcollectvideo*>& collectsfirstimage)
{
    QString str=QString("select * from personalcollect where people='%1'").arg(people);
    qDebug()<<str;
    if(!sq->exec(str))
    {
        qDebug()<<"databasemanage194:sql执行失败";
        return;
    }
    while(sq->next())
    {
        QStringList stl=sq->value("videoids").toString().split(";",Qt::SkipEmptyParts);
        QString firstimage=sq->value("firstimage").toString();
        QString collectname=sq->value("collectname").toString();
        qDebug()<<collectname;
        firstcollectvideo* fri=new firstcollectvideo;
        fri->init(firstimage,collectname);
        collectsfirstimage[collectname]=fri;
        for(int i=0;i<stl.count();i++)
        {
            collects[collectname].push_back(stl.at(i).toInt());
        }
    }
}

void databasemanage::addonevideotocollect(QString people,QString collectname, int videoid)
{
    int collectnum=-1;
    QString videoids;
    //先获取一些信息，在此基础上进行更新
    QString str=QString("select * from personalcollect where people='%1'").arg(people);
    if(!sq->exec(str))
    {
        qDebug()<<"databasemanage194:select sql执行失败";
        return;
    }
    while(sq->next())
    {
        collectnum=sq->value("collectnum").toInt();
        videoids=sq->value("videoids").toString();
    }
    if(collectnum==-1)
        qDebug()<<"databasemanage233: addonevideotocollect is failed";
    else
    {
        collectnum++;
        videoids+=QString::number(videoid)+";";
        QString str=QString("update personalcollect set collectnum=%1 , videoids='%2' where people='%3' and collectname='%4'").arg(collectnum).arg(videoids).arg(people).arg(collectname);
        if(!sq->exec(str))
        {
            qDebug()<<"databasemanage241: update sql执行失败";
        }
    }
}

void databasemanage::loadfriendrelation(QString people, QHash<QString, frienditem *>& friends)
{
    QString str=QString("select * from friendrelation where people='%1'").arg(people);
    if(!sq->exec(str))
        qDebug()<<"databasemanage291: update sql执行失败";
    else{
        while(sq->next())
        {
            frienditem* fri=new frienditem;
            fri->init(sq->value("friend").toString(),sq->value("friendimage").toString(),sq->value("nearestmes").toString(),sq->value("friendudpsocket").toInt());
            friends[sq->value("friend").toString()]=fri;
        }
    }
}

void databasemanage::addfriendrelation(QString people, QString fri, QString friendimage, int friendudpsocket)
{
    QString str=QString("insert into friendrelation(people,friend,friendimage,friendudpsocket) values('%1','%2','%3',%4)").arg(people).arg(fri).arg(friendimage).arg(friendudpsocket);
    if(!sq->exec(str))
        qDebug()<<"databasemanage318: addfriendrelation sql执行失败";
}

void databasemanage::removefriendrelation(QString people, QString fri)
{
    QString str=QString("delete from friendrelation where people='%1' and friend='%2'").arg(people).arg(fri);
    if(!sq->exec(str))
        qDebug()<<"databasemanage325: removefriendrelation sql执行失败";
}

void databasemanage::addonefriendmes(QString people, QString fri, QString content_type, QString content)
{
    QDate curdate=QDate::currentDate();
    QString time=QString("%1-%2-%3 %4").arg(curdate.year()).arg(curdate.month()).arg(curdate.day()).arg(QTime::currentTime().toString());
    QString str=QString("insert into friendmes(people,friend,time,content_type,content) values('%1','%2','%3','%4','%5')").arg(people).arg(fri).arg(time).arg(content_type).arg(content);
    if(!sq->exec(str))
        qDebug()<<"databasemanage309: insert sql执行失败";
}

void databasemanage::loadfriendmes(QString people, QHash<QString,QVector<mysendmes*>>& ms,QHash<QString,QVector<othersendmes*>>& os)
{
    QString str=QString("select * from friendmes where people='%1'").arg(people);
    if(!sq->exec(str))
        qDebug()<<"databasemanage316: load friendmes sql执行失败";
    while(sq->next())
    {
        QString fri=sq->value("friend").toString();
        QString time=sq->value("time").toString();
        QString content_type=sq->value("content_type").toString();
        QString content=sq->value("content").toString();
        mysendmes* md=new mysendmes;
        md->init("",content,time,content_type);
        ms[fri].push_back(md);
    }
    str=QString("select * from friendmes where friend='%1'").arg(people);
    if(!sq->exec(str))
        qDebug()<<"databasemanage316: load friendmes sql执行失败";
    while(sq->next())
    {
        QString fri=sq->value("people").toString();
        QString time=sq->value("time").toString();
        QString content_type=sq->value("content_type").toString();
        QString content=sq->value("content").toString();
        othersendmes* od=new othersendmes;
        od->init("",content,time,content_type);
        os[fri].push_back(od);
    }
}

bool databasemanage::judgefavor(int videoid, QString people)
{
    QString str=QString("select * from favorinfo where people='%1' and videoid=%2").arg(people).arg(videoid);
    if(!sq->exec(str))
        qDebug()<<"databasemanage371: load friendmes sql执行失败";
    if(!sq->first())
        return false;
    else
        return true;
}

bool databasemanage::judgecoin(int videoid, QString people)
{
    QString str=QString("select * from coininfo where people='%1' and videoid=%2").arg(people).arg(videoid);
    if(!sq->exec(str))
        qDebug()<<"databasemanage382: load friendmes sql执行失败";
    if(!sq->first())
        return false;
    else
        return true;
}

bool databasemanage::judegecollect(int videoid, QString people)
{
    QString str=QString("select * from collectinfo where people='%1' and videoid=%2").arg(people).arg(videoid);
    if(!sq->exec(str))
        qDebug()<<"databasemanage393: load friendmes sql执行失败";
    if(!sq->first())
        return false;
    else
        return true;
}

void databasemanage::addfavor(int videoid, QString people)
{
    QString str=QString("insert into favorinfo(people,videoid) values('%1','%2')").arg(people).arg(videoid);
    if(!sq->exec(str))
        qDebug()<<"databasemanage404: load friendmes sql执行失败";
}

void databasemanage::addcoin(int videoid, QString people)
{
    QString str=QString("insert into coininfo(people,videoid) values('%1','%2')").arg(people).arg(videoid);
    if(!sq->exec(str))
        qDebug()<<"databasemanage411: load friendmes sql执行失败";
}

void databasemanage::addcollect(int videoid, QString people)
{
    QString str=QString("insert into collectinfo(people,videoid) values('%1','%2')").arg(people).arg(videoid);
    if(!sq->exec(str))
        qDebug()<<"databasemanage418: load friendmes sql执行失败";
}

void databasemanage::reducefavor(int videoid, QString people)
{
    QString str=QString("delete from  favorinfo where people='%1' and videoid=%2").arg(people).arg(videoid);
    if(!sq->exec(str))
        qDebug()<<"databasemanage404: load friendmes sql执行失败";
}

void databasemanage::reducecoin(int videoid, QString people)
{
    QString str=QString("delete from  coininfo where people='%1' and videoid=%2").arg(people).arg(videoid);
    if(!sq->exec(str))
        qDebug()<<"databasemanage411: load friendmes sql执行失败";
}

void databasemanage::reducecollect(int videoid, QString people)
{
    QString str=QString("delete from  collectinfo where people='%1' and videoid=%2").arg(people).arg(videoid);
    if(!sq->exec(str))
        qDebug()<<"databasemanage418: load friendmes sql执行失败";
}
